﻿using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{
    public class CaseInsensitiveComparer : IComparer<object>
    {
        public int Compare(object x, object y)
        {
            return string.Compare((string)x, (string)y, StringComparison.OrdinalIgnoreCase);
        }
    }
    class YCAAB05DOCReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public OracleConnection OracleDB;
        public OracleCommand orlReadCommand = new OracleCommand();
        public IntexReptServBLClient proxy;
        private Dictionary<string, object> dicParamater;
        public DataSet dsResult;
        public DataSet setUI = new DataSet();
        INTEX.DataBase.SETINTEXDB INTEXDB = new INTEX.DataBase.SETINTEXDB();
        INTEXDBC INTEXDBC;
        public string ReportType = "CONTRACT";
        string ABPAUFFKRS;
        string ABPAUFFLFD;
        string ABPAUFFPOS;
        string WBBTP;
        string DocType;
        string CellName = "";
        string CellValue = "";
        string strCompany;
        string strExcelFile;
        string Today
        {
            get
            {
                DateTime now = DateTime.Now;
                return now.Year.ToString() + Right("00" + now.Month.ToString(), 2) + Right("00" + now.Day.ToString(), 2);
            }
        }
        #endregion
        public YCAAB05DOCReport()
            : base()
        {
        }
        public YCAAB05DOCReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            DocType = dicParamater["DocType"].ToString().Trim();
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
        }
        public override void WriteExcelReport(string fileType)
        {
            if (INTEXDBC == null)
            {
                INTEXDBC = new INTEXDBC(OracleDB, proxy);
            }
            bool IsFTP = false;
            try
            {
                strExcelFile = @"YCA\YCAAB05DOC.xls";
                DataRow dwVKPOS = dsSource.Tables["ABKOPF"].Rows[0];
                string KRS = dwVKPOS["ABKAUFKRS"].ToString().Trim();
                string LFD = dwVKPOS["ABKAUFLFD"].ToString().Trim();
                long flfd = 0;
                flfd = System.Convert.ToInt64(LFD);
                string newfilename = KRS.Trim() + flfd.ToString();
                exAgent.CheckWorkBook(newfilename);
                LocalFile(KRS + LFD);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private string FillInEXCEL()
        {
            bool pricefilled = false;
            string lo = "01";
            double qs = 0, qm = 0, ql = 0, qxl = 0, qtotal = 0;
            try
            {
                string value = "";
                DataRow row = null;
                Dictionary<string, int> colorpos = new Dictionary<string, int>();
                int x = 1, y = 1;
                x = 5; y = 14;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);

                x = 3; y = 30;
                exAgent.SetCellValue(x, y, Today);
                if (dsSource.Tables.Contains("LIEFDAT"))
                {
                    if (dsSource.Tables["LIEFDAT"].Rows.Count > 0)
                    {
                        row = dsSource.Tables["LIEFDAT"].Rows[0];
                        value = row["LSNAME1"].ToString().Trim() + row["LSNAME2"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 4;
                        exAgent.SetCellValue(x, y, value);
                        value = row["LSSTRASSE1"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 13;
                        exAgent.SetCellValue(x, y, value);
                    }
                }
                int rown = -1;
                string processed = "";
                foreach (DataRow dwABPOS in dsSource.Tables["ABPOS"].Rows)
                {
                    lo = dwABPOS["ABPZLO"].ToString();
                    string art = dwABPOS["ABPART"].ToString();
                    string var = dwABPOS["ABPVAR"].ToString();
                    var = Left(var, 2);
                    string color = dwABPOS["ABPFARBE"].ToString();
                    string size = dwABPOS["ABPGROE"].ToString();
                    double qty = System.Convert.ToDouble(dwABPOS["ABPSOLLMENGE7"].ToString()) * 0.01;
                    value = dwABPOS["ABPLIEFDAT"].ToString();
                    x = 4; y = 30;
                    exAgent.SetCellValue(x, y, value);
                    var query = from rows in dsSource.Tables["FSSTAMM"].AsEnumerable()
                                where rows["FSART"].ToString() == art
                                & rows["FSVAR"].ToString() == var
                                select rows;
                    foreach (DataRow rw in query)
                    {
                        exAgent.SetCellValue(20, 1, rw["FSARTPRGR"].ToString());//波段
                        exAgent.SetCellValue(20, 3, art);
                        x = 20; y = 6;
                        exAgent.SetCellValue(x, y, var);
                        value = "品名:" + GetGB2312Value(rw["FSABEZ"].ToString());
                        x = 21; y = 1;
                        exAgent.SetCellValue(x, y, value);
                       
                    }
                    #region====Color===================
                    int posx = 20;
                    int posy = 16;
                    if (!processed.Contains(color + ";"))
                    {
                        processed += color + ";";
                        rown++;
                        posx = 20 + rown * 2;
                        if (rown > 0)
                        {
                            exAgent.RangeCopy(20, 22, posx, posx + 3);
                        }
                        x = posx; y = 14;
                        exAgent.SetCellValue(x, y, color);
                        query = from rows in dsSource.Tables["FSFARBEN"].AsEnumerable()
                                where rows["FSFART"].ToString() == art
                                & rows["FSFVAR"].ToString() == var
                               & rows["FSFFARBE"].ToString() == color
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            value = GetGB2312Value(rw["FSFFBEZ"].ToString());
                            x = 20 + rown * 2 + 1; y = 14;
                            exAgent.SetCellValue(x, y, value);
                        }
                        colorpos.Add(color, posx);

                    }
                    else
                    {
                        posx = colorpos[color];
                    }

                    switch (size.Trim())
                    {
                        case "S":
                            posy = 24;
                            qs += qty;
                            break;
                        case "M":
                            posy = 27;
                            qm += qty;
                            break;
                        case "L":
                            posy = 30;
                            ql += qty;
                            break;
                        case "XL":
                            posy = 33;
                            qxl += qty;
                            break;
                    }
                    qtotal += qty;
                    exAgent.SetCellValue(posx, posy, qty);
                    #endregion
                }
                if (qs > 0) exAgent.SetCellValue(100, 24, qs);
                if (qm > 0) exAgent.SetCellValue(100, 27, qm);
                if (ql > 0) exAgent.SetCellValue(100, 30, ql);
                if (qxl > 0) exAgent.SetCellValue(100, 33, qxl);
                exAgent.SetCellValue(100, 20, qtotal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return lo;
        }
        private void FillInContract()
        {
            bool pricefilled = false;
            try
            {
                string value = "";
                DataRow row = null;
                Dictionary<string, int> colorpos = new Dictionary<string, int>();
                int x = 1, y = 1;
                x = 5; y = 14;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);

                x = 3; y = 30;
                exAgent.SetCellValue(x, y, Today);
                if (dsSource.Tables.Contains("LIEFDAT"))
                {
                    if (dsSource.Tables["LIEFDAT"].Rows.Count > 0)
                    {
                        row = dsSource.Tables["LIEFDAT"].Rows[0];
                        value = row["LSNAME1"].ToString().Trim() + row["LSNAME2"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 4;
                        exAgent.SetCellValue(x, y, value);
                        value = row["LSSTRASSE1"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 13;
                        exAgent.SetCellValue(x, y, value);
                    }
                }
                int rown = -1;
                string processed = "";
                int rowspercolor = 3;
                double vtotal = 0, qtotal = 0;
                foreach (DataRow dwABPOS in dsSource.Tables["ABPOS"].Rows)
                {
                    string art = dwABPOS["ABPART"].ToString();
                    string var = dwABPOS["ABPVAR"].ToString();
                    var = Left(var, 2);
                    string color = dwABPOS["ABPFARBE"].ToString();
                    string size = dwABPOS["ABPGROE"].ToString();
                    double qty = System.Convert.ToDouble(dwABPOS["ABPSOLLMENGE7"].ToString()) * 0.01;
                    value = dwABPOS["ABPLIEFDAT"].ToString();
                    x = 4; y = 30;
                    exAgent.SetCellValue(x, y, value);
                    var query = from rows in dsSource.Tables["FSSTAMM"].AsEnumerable()
                                where rows["FSART"].ToString() == art
                                & rows["FSVAR"].ToString() == var
                                select rows;
                    foreach (DataRow rw in query)
                    {
                        x = 20; y = 1;
                        exAgent.SetCellValue(x, y, art);
                        x = 20; y = 6;
                        exAgent.SetCellValue(x, y, var);
                        value = "品名:" + GetGB2312Value(rw["FSABEZ"].ToString());
                        x = 21; y = 1;
                        exAgent.SetCellValue(x, y, value);
                    }
                    #region====Color===================
                    int posx = 20;
                    int posy = 16;
                    if (!processed.Contains(color + ";"))
                    {
                        processed += color + ";";
                        rown++;
                        posx = 20 + rown * rowspercolor;
                        if (rown > 0)
                        {
                            exAgent.RangeCopy(20, 22, posx, posx + 3);
                        }
                        x = posx; y = 10;
                        exAgent.SetCellValue(x, 11, color);
                        query = from rows in dsSource.Tables["FSFARBEN"].AsEnumerable()
                                where rows["FSFART"].ToString() == art
                                & rows["FSFVAR"].ToString() == var
                               & rows["FSFFARBE"].ToString() == color
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            value = GetGB2312Value(rw["FSFFBEZ"].ToString());
                            x = posx + 1;
                            exAgent.SetCellValue(x, 11, value);
                        }

                        #region====price=======
                        double price = 0;
                        double colortotal = 0;
                        var qp = (from DataRow rp in dsSource.Tables["ABPR"].AsEnumerable()
                                  select rp["ARWERT"].ToString()).Max();
                        price = Convert.ToDouble(qp.ToString()) * 0.001;
                        exAgent.SetCellValue(posx, 17, Math.Round(price, 2));
                        #endregion
                        #region====ColorTotal=======
                        var qxd = from XD in dsSource.Tables["ABPOS"].AsEnumerable()
                                  where XD["ABPAUFKRS"].ToString() == dwABPOS["ABPAUFKRS"].ToString()
                                  & XD["ABPAUFLFD"].ToString() == dwABPOS["ABPAUFLFD"].ToString()
                                  & XD["ABPART"].ToString() == dwABPOS["ABPART"].ToString()
                                  & XD["ABPFARBE"].ToString() == dwABPOS["ABPFARBE"].ToString()
                                  group XD by new
                                   {
                                       ABPFARBE = XD["ABPFARBE"].ToString(),
                                   }
                                      into g
                                      select new
                                      {
                                          KEY = g.Key,
                                          ABPSOLLMENGE7 = g.Sum(XD => Convert.ToDouble(XD.Field<Decimal>("ABPSOLLMENGE7"))),
                                      };

                        foreach (var rw in qxd)
                        {
                            colortotal = rw.ABPSOLLMENGE7 * 0.01;
                            exAgent.SetCellValue(posx, 14, colortotal);
                            exAgent.SetCellValue(posx, 20, Math.Round(price * colortotal));
                            qtotal += colortotal;
                            vtotal += colortotal * price;
                        }
                        #endregion
                        colorpos.Add(color, posx);
                    }
                    else
                    {
                        posx = colorpos[color];
                    }

                    switch (size.Trim())
                    {
                        case "S":
                            posy = 24;
                            break;
                        case "M":
                            posy = 27;
                            break;
                        case "L":
                            posy = 30;
                            break;
                        case "XL":
                            posy = 33;
                            break;
                    }
                    exAgent.SetCellValue(posx, posy, qty);
                    #endregion
                }
                exAgent.SetCellValue(500, 21, vtotal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInWDECK(int startx, int starty, string LO = "01")
        {
            try
            {
                int lines = 3;
                DataTable tb = dsSource.Tables["WDECK"];
                var query = (from rw in tb.AsEnumerable()
                             group rw by new
                             {
                                 ART = rw["WDART"],
                                 VAR = rw["WDVAR"],
                                 COLOR = rw["WDFARBE"],
                             }
                                 into g
                                 select new
                                 {
                                     KEY = g.Key,
                                     QTY = g.Sum(rw => rw.Field<decimal>("WDBEDARF")),
                                 }).OrderBy(b => b.KEY.ART, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.VAR, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.COLOR, new CaseInsensitiveComparer());
                int rowd = -1;
                int x = startx;
                int y = starty;
                foreach (var rw in query)
                {
                    rowd++;
                    string text = "";
                    string art = rw.KEY.ART.ToString();
                    string var = Left(rw.KEY.VAR.ToString() + "   ", 2);
                    string color = rw.KEY.COLOR.ToString();
                    double nwidth = 0, gwidth = 0, grm = 0;
                    int posx = x + rowd * lines;
                    if (rowd > 0)
                    {
                        exAgent.RangeCopy(104, 106, posx, posx + 3);
                    }
                    exAgent.SetCellValue(posx, 1, rw.KEY.ART.ToString() + rw.KEY.VAR.ToString());

                    var qw = from rwx in dsSource.Tables["WASTAMM"].AsEnumerable()
                             where rwx["WAART"].ToString() == art
                             && rwx["WAVAR"].ToString() == var
                             select rwx;
                    foreach (DataRow rwx in qw)
                    {
                        exAgent.SetCellValue(posx + 1, 1, GetGB2312Value(rwx["WABEZ"].ToString()));
                        //wafbrtl,wafbrtu,wafgrqm
                        text = "";
                        text = "可用门幅: " + Math.Round(Convert.ToDouble(rwx["WAFBRTL"].ToString()) * 0.1).ToString()
                        + "   毛门幅: " + Math.Round(Convert.ToDouble(rwx["WAFBRTU"].ToString()) * 0.1).ToString()
                        + "  克重:" + rwx["WAFGRQM"].ToString() + "g/m2       缩水率(JXW)						";
                        exAgent.SetCellValue(posx + 2, 14, text);
                        //exAgent.SetCellValue(x + rowd * lines + 3, 1, exart + "/" + excolor);
                    }
                    exAgent.SetCellValue(posx, 14, rw.KEY.COLOR);

                    var qwF = from rwx in dsSource.Tables["WAFARBEN"].AsEnumerable()
                              where rwx["WAFART"].ToString() == art
                             && rwx["WAFVAR"].ToString() == var
                             && rwx["WAFZUSATZ"].ToString() == color
                              select rwx;
                    foreach (DataRow rwx in qwF)
                    {
                        exAgent.SetCellValue(posx + 1, 14, GetGB2312Value(rwx["WAFBEZ"].ToString()));
                    }
                    //exAgent.SetCellValue(x + rowd * 2, 24, System.Convert.ToDouble(rw.QTY) * 0.01);
                    string filter = "ABAART='" + art + "' AND ABAFARBE='" + color + "'";
                    DataRow[] rows = dsSource.Tables["ICC_ABAUFM"].Select(filter);
                    string ek = "";
                    string processed = "";
                    string exart = "", excolor = "";
                    double planqty = 0;
                    string um = "";
                    double rlfd = 0;
                    DataRow dwData = INTEXDB.WBBEW.NewRow();
                    dwData["WBEWMANDANT"] = "00";
                    dwData["WBEWLO"] = LO;
                    foreach (DataRow row in rows)
                    {
                        dwData["WBEWAUFKRS"] = row["ABAAUFKRS"];
                        dwData["WBEWAUFLFD"] = row["ABAAUFLFD"];
                        dwData["WBEWAUFPOS"] = row["ABAAUFPOS"];
                        exAgent.SetCellValue(posx, 20, Math.Round(Convert.ToDouble(row["RATIO"].ToString()), 2));
                        ek = row["ABAAUFKRS"].ToString()
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFLFD"].ToString())
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFPOS"].ToString());
                        if (!processed.Contains(ek + ";"))
                            processed += ek + ";";
                        exart = row["EXART"].ToString();
                        excolor = row["EXCOLOR"].ToString();
                        planqty += Math.Round(Convert.ToDouble(row["ABAQTYRESERVE"].ToString()), 2);
                        um = row["WAUEBM"].ToString();
                        if (um.Trim() == "")
                        {
                            um = row["UM"].ToString();
                        }
                        rlfd = 0.001 * Convert.ToDouble(row["WAFGRLFD"].ToString());
                        exAgent.SetCellValue(posx, 23, um);
                    }
                    DataSet SResult = INTEXDBC.QueryWBBEW(dwData, 5);
                    double actqty = 0;
                    foreach (DataRow rwb in SResult.Tables[0].Rows)
                    {
                        if (um.Contains("M"))
                        {
                            actqty = Convert.ToDouble(rwb["WBEWMTR"].ToString());

                        }
                        else
                        {
                            actqty = Convert.ToDouble(rwb["WBEWGEW"].ToString());
                        }
                        if (um.Contains("KG"))
                            actqty = actqty * rlfd;
                        actqty = Math.Round(actqty, 2);
                        exAgent.SetCellValue(posx, 27, actqty);
                        exAgent.SetCellValue(posx + 1, 30, rwb["WBLIEFSCHNR"].ToString());
                        /*
                       or1.CommandText = "  SELECT WBLIEFSCHNR,WBKDPART,WBBRT,WBGRQM,WBGRLFD,WBABS,X.* FROM"
                   + " (SELECT WBEWMANDANT, MAX(WBEWRWLFD) AS WBEWRWLFD,SUM(WBEWMTR) AS WBEWMTR,SUM(WBEWGEW) AS WBEWGEW FROM WBBEW"
                   + " WHERE WBEWAUFTYP='B' AND  AND WBEWMANDANT=:WBEWMANDANT AND WBEWAUFKRS=:WBEWAUFKRS AND WBEWAUFLFD=:WBEWAUFLFD AND WBEWAUFPOS=:WBEWAUFPOS AND WBEWLO=:WBEWLO "
                   + "  GROUP BY WBEWMANDANT) X LEFT JOIN WBDAT ON WBEWMANDANT=WBMANDANT AND WBEWRWLFD=WBRWLFD";*/
                    }
                    if (um.Contains("KG"))
                        planqty = planqty * rlfd;
                    planqty = Math.Round(planqty, 2);
                    exAgent.SetCellValue(posx, 24, planqty);
                    exAgent.SetCellValue(posx + 2, 1, "供应商品种色号:"+exart.Trim() + "/" + excolor.Trim());
                    exAgent.SetCellValue(posx, 30, processed);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInFDECK(int startx, int starty)
        {
            try
            {
                int lines = 3;
                string um = "";
                DataTable tb = dsSource.Tables["FDECK"];
                var query = (from rw in tb.AsEnumerable()
                             where rw["FDTYP"].ToString() == "B"
                             group rw by new
                             {
                                 ART = rw["FDART"],
                                 VAR = rw["FDVAR"],
                                 COLOR = rw["FDFARBE"],
                                 SIZE = rw["FDGROE"],
                             }
                                 into g
                                 select new
                                 {
                                     KEY = g.Key,
                                     QTY = g.Sum(rw => rw.Field<decimal>("FDMENGE")),
                                 }).OrderBy(b => b.KEY.ART, new CaseInsensitiveComparer())
                                  .ThenBy(b => b.KEY.VAR, new CaseInsensitiveComparer())
                                  .ThenBy(b => b.KEY.COLOR, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.SIZE, new CaseInsensitiveComparer());
                int rowd = -1;
                int x = startx;
                int y = starty;
                int posx = 0;
                foreach (var rw in query)
                {
                    string art = rw.KEY.ART.ToString();
                    string var = Left(rw.KEY.VAR.ToString() + "   ", 2);
                    string color = rw.KEY.COLOR.ToString();
                    string size = rw.KEY.SIZE.ToString();
                    rowd++;
                    posx = x + rowd * lines;
                    if (rowd > 0)
                    {
                        exAgent.RangeCopy(204, 206, posx, posx + 3);
                    }
                    exAgent.SetCellValue(posx, 1, rw.KEY.ART.ToString() + rw.KEY.VAR.ToString());
                    var qw = from rwx in dsSource.Tables["FSSTAMM"].AsEnumerable()
                             where rwx["FSART"].ToString() == art
                             && rwx["FSVAR"].ToString() == var
                             select rwx;
                    foreach (DataRow rwx in qw)
                    {
                        exAgent.SetCellValue(posx + 1, 1, GetGB2312Value(rwx["FSABEZ"].ToString()));
                    }
                    if (rw.KEY.SIZE.ToString().Trim() != "")
                    {
                        exAgent.SetCellValue(posx, 14, rw.KEY.COLOR + "/" + rw.KEY.SIZE);
                    }
                    else
                    {
                        exAgent.SetCellValue(posx, 14, rw.KEY.COLOR);
                    }
                    var qwF = from rwx in dsSource.Tables["FSFARBEN"].AsEnumerable()
                              where rwx["FSFART"].ToString() == art
                              && rwx["FSFVAR"].ToString() == var
                              && rwx["FSFFARBE"].ToString() == color
                              select rwx;
                    foreach (DataRow rwx in qwF)
                    {
                        exAgent.SetCellValue(posx + 1, 14, GetGB2312Value(rwx["FSFFBEZ"].ToString()));
                    }
                    //exAgent.SetCellValue(posx, 19, rw.KEY.SIZE);
                    exAgent.SetCellValue(posx, 24, System.Convert.ToDouble(rw.QTY));
                    string filter = "ABAART='" + art + "' AND ABAFARBE='" + color + "' AND ABAGROE='" + size + "'";
                    DataRow[] rows = dsSource.Tables["ICC_ABAUFM"].Select(filter);
                    string ek = "";
                    string processed = "";
                    foreach (DataRow row in rows)
                    {
                        um = row["UM"].ToString();
                        exAgent.SetCellValue(posx, 20, row["RATIO"]);
                        ek = row["ABAAUFKRS"].ToString()
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFLFD"].ToString())
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFPOS"].ToString());
                        if (!processed.Contains(ek + ";"))
                            processed += ek + ";";
                    }
                    exAgent.SetCellValue(posx, 23, um);
                    exAgent.SetCellValue(posx, 30, processed);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInMDECK(int startx, int starty)
        {
            try
            {
                DataTable tb = dsSource.Tables["MDECK"];
                var query = (from rw in tb.AsEnumerable()
                             group rw by new
                             {
                                 ART = rw["MDMAT"],
                                 VAR = rw["MDVAR"],
                             }
                                 into g
                                 select new
                                 {
                                     KEY = g.Key,
                                     QTY = g.Sum(rw => rw.Field<decimal>("MDMENGE")),
                                 }).OrderBy(b => b.KEY.ART, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.VAR, new CaseInsensitiveComparer());
                int rowd = -1;
                int x = startx;
                int y = starty;
                foreach (var rw in query)
                {
                    rowd++;
                    string art = rw.KEY.ART.ToString();
                    string var = Left(rw.KEY.VAR.ToString() + "   ", 3);
                    exAgent.SetCellValue(x + rowd * 2, 1, rw.KEY.ART.ToString() + rw.KEY.VAR.ToString());
                    var qw = from rwx in dsSource.Tables["MSSTAMM"].AsEnumerable()
                             where rwx["MSMAT"].ToString() == art
                             && rwx["MSVAR"].ToString() == var
                             select rwx;
                    foreach (DataRow rwx in qw)
                    {
                        exAgent.SetCellValue(x + rowd * 2 + 1, 1, GetGB2312Value(rwx["MSBEZ"].ToString()));
                    }
                    exAgent.SetCellValue(x + rowd * 2, 24, System.Convert.ToDouble(rw.QTY));
                }

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        public DataSet CreateMatrix(DataTable tbABPOS)
        {
            DataSet Return = new DataSet();

            DataTable tbSizeColorMatrix = new DataTable("SizeColorMatrix");
            tbSizeColorMatrix.Columns.Add(new DataColumn("ARTICLE"));
            tbSizeColorMatrix.Columns.Add(new DataColumn("VAR"));
            tbSizeColorMatrix.Columns.Add(new DataColumn("COLOR"));

            Dictionary<string, int> colors = new Dictionary<string, int>();
            foreach (DataRow row in tbABPOS.Rows)
            {
                string color = row["ABPFARBE"].ToString();
                string size = row["ABPGROE"].ToString().Trim();
                double qty = System.Convert.ToDouble(row["ABPSOLLMENGE7"].ToString());
                bool added = false;
                bool newcolor = false;
                int yc = tbSizeColorMatrix.Columns.Count;
                int y = 0;
                added = false;
                DataRow rmx = null;
                var query = from DataRow rm in tbSizeColorMatrix.AsEnumerable()
                            where rm["颜色"].ToString() == color
                            select rm;
                foreach (DataRow rm in query)
                {
                    rmx = rm;
                }
                if (rmx == null)
                {
                    rmx = tbSizeColorMatrix.NewRow();
                    newcolor = true;
                }
                rmx["颜色"] = color;

                added = false;
                foreach (DataColumn m in tbSizeColorMatrix.Columns)
                {
                    if (m.ColumnName == size)
                    {
                        added = true;
                        break;
                    }
                    y++;
                }
                if (!added)//未找到列；
                {
                    tbSizeColorMatrix.Columns.Add(new DataColumn(size));
                }
                yc = tbSizeColorMatrix.Columns.Count;
                rmx[y] = qty;
                if (newcolor)
                {
                    tbSizeColorMatrix.Rows.Add(rmx);
                }
            }

            Return.Merge(tbSizeColorMatrix);
            return Return;
        }
        private void HideNullRows(int pages)
        {
            try
            {
                exAgent.HideRows((pages + 1) * 25 + 1, 1, 500, 999);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private void CheckDirectory(string root, string sub, string year, string month, string day)
        {
            try
            {
                string drlocal = root;
                string dr0 = "";

                #region========================LOCAL=====================
                drlocal = Path.Combine(PathHelper.ApplicationPath, @"Report");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, "Sales");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                #endregion
                drlocal = root;
                drlocal = Path.Combine(drlocal, sub);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private string CreateFile(string orderno)
        {
            string filename = "";
            string sheetName = "";
            string Standard = "AB05DOC";
            string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\YCA\YCAAB05DOC.xls");
            string newfilename = "";
            string localFile = "";
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(filePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                newfilename = orderno;
                switch (ReportType)
                {
                    case "CONTRACT":
                        Standard = "AB05CTC";
                        exAgent.CreateNewSheet(newfilename, Standard, orderno.Replace(".xls", ""), pwd);
                        filename = exAgent.FileName;
                        FillInContract();
                        break;
                    case "PLAN":
                        Standard = "AB05DOC";
                        exAgent.CreateNewSheet(newfilename, Standard, orderno.Replace(".xls", ""), pwd);
                        filename = exAgent.FileName;
                        string lo = FillInEXCEL();
                        int startx = 104, starty = 1;
                        FillInWDECK(startx, starty, lo);
                        /*startx = 83;
                        FillInMDECK(startx, starty);*/
                        startx = 204;
                        FillInFDECK(startx, starty);
                        break;
                }
                localFile = exAgent.FileName;
                exAgent.Save();
                exAgent.Close();
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }

        private void LocalFile(string ordernumer)
        {
            DataRow dwABKOPF = dsSource.Tables["ABKOPF"].Rows[0];
            if (ReportType == "CONTRACT")
                ordernumer = "CTC" + ordernumer;
            else
                ordernumer = "PLAN" + ordernumer;
            string fileabsname = ordernumer + ".xls";
            string localFile = @"C:\Windows\Temp\" + fileabsname;
            string tempfile = "";
            try
            {
                tempfile = CreateFile(fileabsname);
                localFile = @"C:\IntexReport\生产单\" + fileabsname;
                if (System.IO.File.Exists(localFile))
                {
                    System.IO.File.Delete(localFile);
                }
                File.Move(tempfile, localFile);
                System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
    }
}